///////// this code cleans call reports /////////////
/// we do not provide the raw data in this replication package
/// the raw call reports can be downloaded from https://cdr.ffiec.gov/public/PWS/DownloadBulkData.aspx 

global raw "Replication_Package\Data\raw\"
global processed "Replication_Package\Data\processed\"
global output "Replication_Package\Output\"


////////////////////////////////////////////////////////////////////////////////
//////////// 2024 q2 /////////////

cd "${raw}FFIEC CDR Call Bulk All Schedules 06302024\"

import delimited "FFIEC CDR Call Bulk POR 06302024.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2024q2",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 06302024\" files "FFIEC*.txt", respectcase

foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2024q2"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2024q2",replace
}
use "${raw}callreport_2024q2",clear 
destring,replace 
save,replace 

//////////// 2024 q1 /////////////

cd "${raw}FFIEC CDR Call Bulk All Schedules 03312024\"

import delimited "FFIEC CDR Call Bulk POR 03312024.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2024q1",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 03312024\" files "FFIEC*.txt", respectcase

foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2024q1"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2024q1",replace
}


use "${raw}callreport_2024q1",clear 
destring,replace 
save,replace 


//////////// 2023 q4 /////////////

cd "${raw}FFIEC CDR Call Bulk All Schedules 12312023\"

import delimited "FFIEC CDR Call Bulk POR 12312023.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2023q4",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 12312023\" files "FFIEC*.txt", respectcase

foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2023q4"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2023q4",replace
}


use "${raw}callreport_2023q4",clear 
destring,replace 
save,replace


//////////// 2023 q3 /////////////
cd "${raw}FFIEC CDR Call Bulk All Schedules 09302023\"

import delimited "FFIEC CDR Call Bulk POR 09302023.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2023q3",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 09302023\" files "FFIEC*.txt", respectcase

foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2023q3"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2023q3",replace
}


use "${raw}callreport_2023q3",clear 
destring,replace 
save,replace 

//////////// 2023 q2 /////////////
cd "${raw}FFIEC CDR Call Bulk All Schedules 06302023\"

import delimited "FFIEC CDR Call Bulk POR 06302023.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2023q2",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 06302023\" files "FFIEC*.txt", respectcase

foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2023q2"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2023q2",replace
}


use "${raw}callreport_2023q2",clear 
destring,replace 
save,replace 

//////////// 2023 q1 /////////////

cd "${raw}FFIEC CDR Call Bulk All Schedules 03312023\"


import delimited "FFIEC CDR Call Bulk POR 03312023.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2023q1",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 03312023\" files "FFIEC*.txt", respectcase
foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2023q1"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2023q1",replace
}


use "${raw}callreport_2023q1",clear 
destring,replace 
save,replace 

//////////// 2022 q1 /////////////

cd "${raw}FFIEC CDR Call Bulk All Schedules 03312022\"

import delimited "FFIEC CDR Call Bulk POR 03312022.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2022q1",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 03312022\" files "FFIEC*.txt", respectcase
foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2022q1"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2022q1",replace
}


use "${raw}callreport_2022q1",clear 
destring,replace 
save,replace 

//////////// 2022 q2 /////////////

cd "${raw}FFIEC CDR Call Bulk All Schedules 06302022\"
import delimited "FFIEC CDR Call Bulk POR 06302022.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2022q2",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 06302022\" files "FFIEC*.txt", respectcase
foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2022q2"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2022q2",replace
}

use "${raw}callreport_2022q2",clear 
destring,replace 
save,replace 

//////////// 2022 q3 /////////////

cd "${raw}FFIEC CDR Call Bulk All Schedules 09302022\"

import delimited "FFIEC CDR Call Bulk POR 09302022.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2022q3",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 09302022\" files "FFIEC*.txt", respectcase
foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2022q3"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2022q3",replace
}

use "${raw}callreport_2022q3",clear 
destring,replace 
save,replace 



//////////// 2022 q4 /////////////
cd "${raw}FFIEC CDR Call Bulk All Schedules 12312022\"

import delimited "FFIEC CDR Call Bulk POR 12312022.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_2022q4",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 12312022\" files "FFIEC*.txt", respectcase
foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2022q4", force
	keep if _merge==3
	drop _merge
	save "${raw}callreport_2022q4",replace
}


use "${raw}callreport_2022q4",clear 
destring,replace 
save,replace 



//////////// 2021 q4 /////////////
cd "${raw}FFIEC CDR Call Bulk All Schedules 12312021\"

 import delimited "FFIEC CDR Call Bulk POR 12312021.txt", varnames(1) clear
 destring idrssd, replace force
 drop if idrssd==.
 bys idrssd: drop if _n>1
 save "${raw}callreport_2021q4",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules 12312021\" files "FFIEC*.txt", respectcase
 foreach file in `files' {
	import delimited "`file'", varnames(1) clear
 	destring idrssd, replace force
	drop if idrssd==.
 	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_2021q4"
 	keep if _merge==3
	drop _merge
 	save "${raw}callreport_2021q4",replace
 }


 use "${raw}callreport_2021q4",clear 
 destring,replace 
 save,replace 

//////////// 2021 q1-q3 /////////////
set more off 
foreach x in "03312021" "06302021" "09302021"{
cd "${raw}FFIEC CDR Call Bulk All Schedules `x'\"

import delimited "FFIEC CDR Call Bulk POR `x'.txt", varnames(1) clear
destring idrssd, replace force
drop if idrssd==.
bys idrssd: drop if _n>1
save "${raw}callreport_`x'",replace

local files : dir "${raw}FFIEC CDR Call Bulk All Schedules `x'\" files "FFIEC*.txt", respectcase

foreach file in `files' {
	import delimited "`file'", varnames(1) clear
	destring idrssd, replace force
	drop if idrssd==.
	bys idrssd: drop if _n>1
	merge 1:1 idrssd using "${raw}callreport_`x'"
	keep if _merge==3
	drop _merge
	save "${raw}callreport_`x'",replace
}


use "${raw}callreport_`x'",clear 
destring,replace 
save,replace 
}

use "${raw}callreport_03312021",clear
save "${raw}callreport_2021q1",replace 
erase "${raw}callreport_03312021.dta"

use "${raw}callreport_06302021",clear
save "${raw}callreport_2021q2",replace 
erase "${raw}callreport_06302021.dta"

use "${raw}callreport_09302021",clear
save "${raw}callreport_2021q3",replace 
erase "${raw}callreport_09302021.dta"


////////// construct and combine //////////


set more off 
forvalue x = 2021(1)2023{ // repeat for 2024q1-q2
	forvalue y=1(1)4{
	use "${raw}callreport_`x'q`y'",clear

// form 031 adjustment to form 041 
	foreach i in "2170" "0081" "0071" "jj34"  "1773"  "ja22" "b989" "5369" "b529" "3545" "2145" ///assets 
	 "a570" "a571" "a572" "a573" "a574" "a575" "g091" "g094" "g097" "g100" "5369" "g483" "g488"  "f055" "f056" "f057" "f058" /// loans 
	"0211" "0213" "1286" "1287" "ht50" "ht51" "ht52" "ht53" "8496" "8497" "8498" "8499" "g300" "g301" "g302" "g303" /// security
    "g304" "g305" "g306" "g307" "g308" "g309" "g310" "g311" "g312" "g313" "g314" "g315" "g316" "g317" "g318" "g319" /// security
	"g320" "g321" "g322" "g323" "k142" "k143" "k144" "k145" "k146" "k147" "k148" "k149" "k150" "k151" "k152" "k153" /// security
	"k154" "k155" "k156" "k157" "c026" "c988" "c989" "c027" "ht58" "ht59" "ht60" "ht61" "1737" "1738" "1739" "1741" /// security
	"1742" "1743" "1744" "1746" "1754" "1771" "1772" "1773" "a549" "a550" "a551" "a552" "a553" "a554" "a555"  /// security 
	"a556" "a557" "a558" "a559" "a560" "a561" "a562" /// security
	"b995" "3548" "3190" "3200" "2930" "2948" //// liability
	"3838" "3230" "3839" "3632" "3210" "3000" "g105" "3300" /// Equity
	"a589" "8725" /// derivative
	{
 replace rcon`i' = rcfd`i' if missing(rcon`i')
 }

 
 // past due 
 egen past_due_30_89_resiloan = rowtotal(rcon5398 rconc236 rconc238)
 egen past_due_90_resiloan = rowtotal(rcon5399 rconc237 rconc239)
 egen past_due_nonaccrual_resiloan = rowtotal(rcon5400 rconc229 rconc230)

 egen past_due_30_89_cre = rowtotal(rconf172 rconf173 rcon3493 rcon3499 rconf178 rconf179)
 egen past_due_90_cre = rowtotal(rconf174 rconf175 rcon3494 rcon3500 rconf180 rconf181)
 egen past_due_nonaccrual_cre = rowtotal(rconf176 rconf177 rcon3495 rcon3501 rconf182 rconf183)

ren rcon1406 past_due_30_89_total
ren rcon1407 past_due_90_total 
ren rcon1403 past_due_non_accrual_total 


 // interest income 

 ren riad4435 interest_income_resiloan 
 ren riad4436 interest_income_other_reloan
 ren riad4024 interest_income_farm 
 ren riad4012 interest_income_ciloan 
 egen interest_income_hhloan = rowtotal(riadb485 riadb486)
 ren riad4010 interest_income_loan
 
 ren riadb489 interest_income_mbs 
 ren riadb488 interest_income_treasury
 ren riad4060 interest_income_other_sec 
 
// charge-offs 
egen charge_off_resiloan=rowtotal(riad5411 riadc234 riadc235)
egen recovery_resiloan = rowtotal(riad5412 riadc217 riadc218)

egen charge_off_creloan =rowtotal(riad3588)
egen recovery_creloan = rowtotal(riad3589)
egen charge_off_other_reloan = rowtotal(riadc891 riadc893 riad3584 riadc895 riadc897) 
egen recovery_other_reloan = rowtotal(riadc892 riadc894 riad3585 riadc896 riadc898)

egen charge_off_ciloan = rowtotal(riad4638)
egen recovery_ciloan = rowtotal(riad4608)
egen charge_off_hhloan = rowtotal(riadb514 riadk129 riadk205)
egen recovery_hhloan = rowtotal(riadb515 riadk133 riadk206)

egen charge_off_total = rowtotal(riad4635)
egen recovery_total=rowtotal(riad4605)

// loan loss provision 
ren riadjj33 loss_provision 

 // derivitative 
ren rcona589 rate_swap_liability
ren rcon8725 rate_swap_total 
//// form 041
// Total asset 
ren rcon2170 total_asset
// cash  
egen cash=rowtotal(rcon0081 rcon0071)
// security
egen security = rowtotal(rconjj34 rcon1773 rconja22)
	ren rconjj34 security_htm // hold to maturity; on balance sheet, the amortized values are used to calculate asset values for these securities
		ren rcon0211 treasury_htm_amortize
		ren rcon0213 treasury_htm_fair
			
		ren rcon8496 state_sec_htm_amortize 
		ren rcon8497 state_sec_htm_fair
		egen agency_rmbs_htm_amortize = rowtotal(rcong300 rcong304 rcong308) 
		egen agency_rmbs_htm_fair = rowtotal(rcong301 rcong305 rcong309)
			
		egen other_rmbs_htm_amortize = rowtotal(rcong312 rcong316 rcong320) 
		egen other_rmbs_htm_fair = rowtotal(rcong313 rcong317 rcong321)
		egen agency_cmbs_htm_amortize = rowtotal(rconk142 rconk146)
		egen agency_cmbs_htm_fair = rowtotal(rconk143 rconk147)
		egen other_cmbs_htm_amortize = rowtotal(rconk150 rconk154)
		egen other_cmbs_htm_fair = rowtotal(rconk151 rconk155)
		egen abs_htm_amortize = rowtotal(rconc026 rconht58 rcon1737 rcon1742)
		egen abs_htm_fair = rowtotal(rconc988 rconht59 rcon1738 rcon1743)
		// there are other categories like SBA guaranteed loan pool certificates... etc
		
	ren rcon1773 security_hfs // available for sale; on balance sheet, the fair market values are used on calculate asset values for these securities 
		ren rcon1286 treasury_hfs_amortize
		ren rcon1287 treasury_hfs_fair
		ren rcon8498 state_sec_hfs_amortize 
		ren rcon8499 state_sec_hfs_fair
		egen agency_rmbs_hfs_amortize = rowtotal(rcong302 rcong306 rcong310) 
		egen agency_rmbs_hfs_fair = rowtotal(rcong303 rcong307 rcong311)
		egen other_rmbs_hfs_amortize = rowtotal(rcong314 rcong318 rcong322) 
		egen other_rmbs_hfs_fair = rowtotal(rcong315 rcong319 rcong323)
		egen agency_cmbs_hfs_amortize = rowtotal(rconk144 rconk148)
		egen agency_cmbs_hfs_fair = rowtotal(rconk145 rconk149)
		egen other_cmbs_hfs_amortize = rowtotal(rconk152 rconk156)
		egen other_cmbs_hfs_fair = rowtotal(rconk153 rconk157)
		egen abs_hfs_amortize = rowtotal(rconc989 rconht60 rcon1739 rcon1744)
		egen abs_hfs_fair = rowtotal(rconc027 rconht61 rcon1741 rcon1746)
		// there are other categories like SBA guaranteed loan pool certificates... etc
		
	ren rconja22 security_equity // equity securities with readily determinable fair values not held for trading 

// rerepo 	
ren rconb987 fed_fund_sold 
ren rconb989 rerepo
// loan 
egen total_loan = rowtotal(rcon5369 rconb529)
	ren rcon5369 total_loan_hfs
	ren rconb529 total_loan_hfi

	egen reloan = rowtotal(rconf158 rconf159 rcon1420 rcon1797 rcon5367 rcon5368 rcon1460 rconf160 rconf161)
		egen reloan_land_dev = rowtotal(rconf158 rconf159)
		egen reloan_farmland = rowtotal(rcon1420)
		egen reloan_residential1to4 = rowtotal(rcon1797 rcon5367 rcon5368)
	
		egen reloan_residential5 = rowtotal(rcon1460)
		egen reloan_nonfarm_noresi = rowtotal(rconf160 rconf161)
	ren rcon1590 agloan
	ren rcon1766 ciloan
	egen ci = rowtotal(rcon1763 rcon1764)
	replace ciloan = ci if missing(ciloan)
	drop ci 
	
	egen hhloan = rowtotal(rconb538 rconb539 rconk137 rconk207)
	ren rconj454 loans_to_nonbank 
		
	ren rconj464 other_loan
    // FV 
	ren rcong091 fv_reloan_htm 
	ren rcong094 fv_ciloan_htm
	ren rcong097 fv_hhloan_htm 
	ren rcong100 fv_other_htm 
	ren rcong483 fv_loan_hfs 
	ren rcong488 fv_loan_htm 
	
// trading assets 	
ren rcon3545 trading_asset

// other asset categories 
ren rcon2145 fixed_asset

//// assets by maturity 
// security - treasury 
ren rcona549 treasury_3mo 
ren rcona550 treasury_1yr
ren rcona551 treasury_3yr
ren rcona552 treasury_5yr 
ren rcona553 treasury_15yr
ren rcona554 treasury_20yr 
// security - rmbs 
ren rcona555 rmbs_3mo 
ren rcona556 rmbs_1yr
ren rcona557 rmbs_3yr
ren rcona558 rmbs_5yr
ren rcona559 rmbs_15yr
ren rcona560 rmbs_20yr
ren rcona561 other_rmbs_3yrless 
ren rcona562 other_rmbs_3yrmore
// loans - r-mortgage
ren rcona564 reloan1to4_3mo
ren rcona565 reloan1to4_1yr
ren rcona566 reloan1to4_3yr 
ren rcona567 reloan1to4_5yr
ren rcona568 reloan1to4_15yr 
ren rcona569 reloan1to4_20yr 
// loans - other
ren rcona570 all_other_loan_3mo
ren rcona571 all_other_loan_1yr
ren rcona572 all_other_loan_3yr
ren rcona573 all_other_loan_5yr
ren rcona574 all_other_loan_15yr
ren rcona575 all_other_loan_20yr
	
///////////////////////////////////////////////////////////////////////////////
//// Total Liability 
ren rcon2948 total_liability

// total deposit 
ren rcon2200 dom_deposit
ren rcfn2200 foreign_deposit

	// transaction or saving 
	ren rcon2215 trans_account_deposit
		ren rcon2210 demand_deposit 
	ren rcon2385 nontrans_account_deposit
		egen savings_deposit = rowtotal(rcon6810 rcon0352) // new
		egen time_deposit_insured = rowtotal(rcon6648 rconj473) // new
		
	// insured vs uninsured (250k - insurance limit)	
	egen insured_deposit_account=rowtotal(rconf049 rconf045) 
	egen insured_account_num = rowtotal(rconf050 rconf046)
	egen uninsured_deposit_account=rowtotal(rconf051 rconf047)
	egen uninsured_account_num = rowtotal(rconf052 rconf048)
	
	gen insured_deposit = insured_deposit_account+uninsured_account_num*250
	gen uninsured_deposit = uninsured_deposit_account - uninsured_account_num*250
	// uninsured time deposit with one more year to maturity 
	//ren rconk220 uninsured_short_broker_deposit
	ren rconj474 uninsured_tot_time_deposit
     	egen uninsured_long_time_account = rowtotal(rconhk14 rconhk15) 
	    ren rconk222 uninsured_short_time_deposit
	// ren rcon5597 uninsured_deposit_direct 


// repo 
ren rconb993 fed_fund_purchase
ren rconb995 repo

// trading liability 
ren rcon3548 trading_liab
ren rcon3190 other_borrowed_money
	egen fed_home_loan_bank_adv = rowtotal(rconf055 rconf056 rconf057 rconf058)	
	egen other_borr = rowtotal(rconf060 rconf061 rconf062 rconf063)
			
ren rcon3200 subordinated_debt
	
ren rcon2930 other_liab

///////////////////////////////////////////////////////////////////////////////
// total equity 
ren rcong105 total_equity

ren rcon3838 preferred_stock
ren rcon3230 common_stock
ren rcon3839 surplus
ren rcon3632 retained_earning
ren rcon3210 total_bank_equity
ren rcon3000 minority_interest_in_subsidiary

// risk weight asset and capital 
replace rcoa8274 = rcfa8274 if missing(rcoa8274)
replace rcoaa223 = rcfaa223 if missing(rcoaa223)
replace rcoa7206 = rcfa7206 if missing(rcoa7206)
ren rcoa8274 tier1cap
ren rcoaa223 risk_based_asset
ren rcoa7206 risk_based_tier1_ratio

ren text4461 item1_name 
ren text4462 item2_name 
ren text4463 item3_name 
ren riad4461 item1 
ren riad4462 item2 
ren riad4463 item3 


///////////////////////////////////////////////////////////////////////////////
// [new] interest rate 
 ren riad4508 interest_exp_transaction_account
 ren riad0093 interest_exp_saving_deposit
 //ren riada517 interest_exp_time_insured
  //ren riada518 interest_exp_time_uninsured
 ren riadhk03 interest_exp_time_insured
 ren riadhk04 interest_exp_time_uninsured 

 ren riad4180 interest_exp_repo
 ren riad4172 interest_exp_foreign
 ren riad4185 interest_exp_other_borrowed
 ren riad4200 interest_exp_subord
// ren rcon2385 nontransact_deposit
 egen insured_time_dep = rowtotal(rcon6648 rconj473)
 
 

// Interest rate
egen interest_insured = rowtotal(interest_exp_transaction_account interest_exp_time_insured)
egen interest_uninsured = rowtotal(interest_exp_saving_deposit interest_exp_time_uninsured interest_exp_repo interest_exp_foreign interest_exp_other_borrowed interest_exp_subord)
egen insured_dep_for_interest = rowtotal(trans_account_deposit insured_time_dep)
gen uninsured_nontransact = nontrans_account_deposit-insured_time_dep
egen uninsured_dep_for_interest = rowtotal(uninsured_nontransact foreign_deposit repo other_borrowed_money subordinated_debt)
 
// [New] Other borrowing 
replace rconb571 = rcfdb571 if missing(rconb571)
ren rconb571 other_borrowing_1yr

// [New] Interest expense
ren riad4073 interest_expense

// [New] Interest income
ren riad4107 interest_income
ren riad4074 interest_income_net

// [New] Return on assets = Net income / Total assets
ren riad4340 net_income

// [New] Net interest margin = Net interest income / Earning assets 
// I followed the guide here https://cdr.ffiec.gov/CDRDownload/CDR/UserGuide/v148/FFIEC%20UBPR%20User%20Guide%20Balance%20Sheet%20$--Page%204_2023-10-26.PDF to calculate the earning asset
// [Loans and leases]
replace rcfdb529 = total_loan_hfi if missing(rcfdb529)
replace rcfd5369 = total_loan_hfs if missing(rcfd5369)
egen loans_leases = rowtotal(rcfdb529 rcfd5369)
// Total investment
// [Securities]
replace rcfd8496 = state_sec_htm_amortize if missing(rcfd8496)
replace rcfd8499 = state_sec_hfs_fair if missing(rcfd8499)
replace rcfd1742 = rcon1742 if missing(rcfd1742)
replace rcfd1746 = rcon1746 if missing(rcfd1746)
egen securities = rowtotal(rcfd8496 rcfd8499 rcfd1742 rcfd1746)
// [Interest bearing balance]
replace rcfd0071 = rcon0071 if missing(rcfd0071)
ren rcfd0071 interest_bear_balance
// [Federal Funds Sold and Resales]
replace rcfdb989 = rerepo if missing(rcfdb989)
egen fed_fund_sold_resales = rowtotal(rcfdb989 fed_fund_sold)
// [Trading account]
replace rcfd3545 = trading_asset if missing(rcfd3545)
ren rcfd3545 treading_act
// [U.S. Treasury and Agency Securities]
foreach i in "rcong1293" "rcfd1293" { // generate 'existingof' variables in the guide
	capture confirm variable `i'
	if !_rc {
		display "`i' exists"
	}
	else {
		gen `i' = 0
	}
}
replace rcfd0211 = treasury_htm_amortize if missing(rcfd0211)
replace rcfdht50 = rconht50 if missing(rcfdht50) // This is referred as rcfd1289/rcon1289 in the guide, but this identifier is not found in the call report, so I search by the variable name
//replace rcfd1294 = rcon1294 if missing(rcfd1294) In the guide, this variable is exactly the same as the previous one
replace rcfd1287 = treasury_hfs_fair if missing(rcfd1287)
replace rcfdht53 = rconht53 if missing(rcfdht53) // This is referred as rcfd1298/rcon1298 in the guide, but this identifier is not found in the call report, so I search by the variable name
// These are remaining catagories for U.S. Treasury and Agency Securities, first variable calculate the amount for REPORTING FORM NUMBER 31, the second varaible calculate the amount for REPORTING FORM NUMBER 41 
egen others_31 = rowtotal(rcfdg300 rcfdg304 rcfdg312 rcfdg316 rcfdg303 rcfdg307 rcfdg315 rcfdg319 rcfdht50 rcfd1293 rcfdht53) //
egen others_41 = rowtotal(rcong300 rconht54 rcong304 rcong312 rcong316 rcong303 rconht57 rcong307 rcong315 rcong319 rconht50 rcong1293 rconht53) //
// We can safely do this because for either type, the others for the other type would be 0
gen others_us_treasury = max(others_31, others_41)
egen us_treasury_agency_securities = rowtotal(others_us_treasury rcfd0211 rcfd1287 rcfdht53 rcfdht50) 
// [All Other Securities]
foreach i in "rcfda511" "rcona511" "rcfdg336" "rcfdg340" "rcfdg344" "rcong336" "rcong340" "rcong344" "rcfdg339" "rcfdg343" "rcfdg347" "rcong339" "rcong343" "rcong347" { // generate 'existingof' variables in the guide
	capture confirm variable `i'
	if !_rc {
		display "`i' exists"
	}
	else {
		gen `i' = 0
	}
}
replace rcfd1737 = rcon1737 if missing(rcfd1737)
replace rcfd1741 = rcon1741 if missing(rcfd1741)
replace rcfda511 = rcona511 if missing(rcfda511)
replace rcfdja22 = security_equity if missing(rcfdja22)
replace rcfdc026 = rconc026 if missing(rcfdc026)
replace rcfdc027 = rconc027 if missing(rcfdc027)
egen other_sec_31 = rowtotal(rcfdg308 rcfdg311 rcfdg320 rcfdg323 rcfdk142 rcfdk146 rcfdk150 rcfdk154 rcfdk145 rcfdk149 rcfdk153 rcfdk157)
egen other_sec_41 = rowtotal(rcong308 rcong311 rcong320 rcong323 rconk142 rconk146 rconk150 rconk154 rconk145 rconk149 rconk153 rconk157)
// Again, we can safely do this because for either type, the others for the other type would be 0
gen other_sec = max(other_sec_31, other_sec_41)
egen abs_amortized_cost_31 = rowtotal(rcfdht58 rcfdg336 rcfdg340 rcfdg344)
egen abs_amortized_cost_41 = rowtotal(rconht58 rcong336 rcong340 rcong344)
gen abs_amortized_cost = max(abs_amortized_cost_31, abs_amortized_cost_41)
egen abs_fairV_31 = rowtotal(rcfdht61 rcfdg339 rcfdg343 rcfdg347)
egen abs_fairV_41 = rowtotal(rconht61 rcong339 rcong343 rcong347)
gen abs_fairV = max(abs_fairV_31, abs_fairV_41)
egen other_securities = rowtotal(abs_fairV abs_amortized_cost other_sec rcfdc027 rcfdc026 rcfd1741 rcfd1737 rcfda511 rcfdja22)
egen total_investment = rowtotal(securities interest_bear_balance fed_fund_sold_resales treading_act us_treasury_agency_securities other_securities)
egen earning_assets = rowtotal(loans_leases total_investment)


drop rcon* riad* text* v* rcfd* rcfn* rcfa* rcfw* rcoa* te0*

	save "${raw}callreport_`x'q`y'_clean",replace
	
}
}
use "${raw}callreport_2021q1_clean",clear
gen month = 3 
gen year = 2021
append using "${raw}callreport_2021q2_clean"
replace month = 6 if missing(month)
replace year = 2021 if missing(year)
append using "${raw}callreport_2021q3_clean"
replace month = 9 if missing(month)
replace year = 2021 if missing(year)
append using "${raw}callreport_2021q4_clean"
replace month = 12 if missing(month)
replace year = 2021 if missing(year)

append using "${raw}callreport_2022q1_clean"
replace month = 3 if missing(month)
replace year = 2022  if missing(year)
append using "${raw}callreport_2022q2_clean"
replace month = 6 if missing(month)
replace year = 2022 if missing(year)
append using "${raw}callreport_2022q3_clean"
replace month = 9 if missing(month)
replace year = 2022 if missing(year)
append using "${raw}callreport_2022q4_clean"
replace month = 12 if missing(month)
replace year = 2022 if missing(year)

append using "${raw}callreport_2023q1_clean"
replace month = 3 if missing(month)
replace year = 2023 if missing(year)
append using "${raw}callreport_2023q2_clean"
replace month = 6 if missing(month)
replace year = 2023 if missing(year)
append using "${raw}callreport_2023q3_clean"
replace month = 9 if missing(month)
replace year = 2023 if missing(year)
append using "${raw}callreport_2023q4_clean"
replace month = 12 if missing(month)
replace year = 2023 if missing(year)

append using "${raw}callreport_2024q1_clean"
replace month = 3 if missing(month)
replace year = 2024 if missing(year)

append using "${raw}callreport_2024q2_clean"
replace month = 6 if missing(month)
replace year = 2024 if missing(year)
save "${raw}callreport_2124_clean",replace

use "${raw}callreport_2022q1_clean.dta",clear 
gen year = 2022 
gen quarter = 1 
save,replace 
erase "${raw}callreport_2022q2_clean.dta"
erase "${raw}callreport_2022q3_clean.dta"
erase "${raw}callreport_2022q4_clean.dta"
erase "${raw}callreport_2023q1_clean.dta"
erase "${raw}callreport_2023q2_clean.dta"
erase "${raw}callreport_2023q3_clean.dta"
erase "${raw}callreport_2023q4_clean.dta"
erase "${raw}callreport_2024q1_clean.dta"
erase "${raw}callreport_2024q2_clean.dta"


use "${raw}callreport_2124_clean.dta",clear 

// create balance sheet
// asset ratios

foreach x of var cash security *_amortize *_fair  fed_fund_sold rerepo total_loan total_loan_hfs total_loan_hfi reloan* agloan ciloan hhloan loans_to_nonbank other_loan trading_asset fixed_asset {
gen r_`x' = 100*`x'/total_asset
replace r_`x' = 0 if missing(r_`x')
}

drop r_reloan1to4_*

// find the percentage of each type of asset by maturity 
foreach x of var t*_3mo t*_1yr t*_3yr t*_5yr t*_15yr t*_20yr  rm*_3mo rm*_1yr rm*_3yr rm*_5yr rm*_15yr rm*_20yr other_rmbs_3yrless other_rmbs_3yrmore  {
gen r_`x' = 100*`x'/security 
}

foreach x of var reloan*_3mo reloan*_1yr reloan*_3yr reloan*_5yr reloan*_15yr reloan*_20yr a*_3mo a*_1yr a*_3yr a*_5yr a*_15yr a*_20yr{
gen r_`x' = 100*`x'/total_loan 
}



gen r_other_reloan = r_reloan - r_reloan_residential1to4-r_reloan_residential5 
gen r_other_asset = 100-r_cash-r_security-r_fed_fund_sold-r_rerepo-r_total_loan-r_trading_asset 
foreach x in "treasury" "agency_rmbs" "other_rmbs" "agency_cmbs" "other_cmbs" "abs"{
gen r_`x' = r_`x'_htm_amortize+r_`x'_hfs_fair
}
gen r_rmbs = r_agency_rmbs + r_other_rmbs 
gen r_cmbs = r_agency_cmbs + r_other_cmbs 
gen r_other_sec = r_security - r_treasury - r_rmbs - r_cmbs -  r_abs 

// liability + equity 
foreach x of var total_liability total_equity preferred_stock common_stock surplus retained_earning total_bank_equity  dom_deposit foreign_deposit insured_deposit* uninsured_deposit*  fed_fund_purchase repo trading_liab other_borrowed_money subordinated_debt other_liab {
gen r_`x' = 100*`x'/total_asset
}

replace r_other_liab = r_other_liab+r_trading_liab+r_other_borrowed_money+r_subordinated_debt
sum total_asset, detail 

// define small, large  https://www.ffiec.gov/cra/pdf/2023_Asset_Size_Threshold.pdf; https://www.federalreserve.gov/newsevents/pressreleases/bcreg20211216a.htm
egen size = mean(total_asset), by(idrssd)
gen size_bin = "small" if size<1384000 // 1.384B
replace size_bin = "gsib" if size>250000000 // 250B
replace size_bin = "large" if missing(size_bin)

// label variable 
replace total_asset = total_asset/1000000
label var total_asset "Total Asset (Billion)"
label var r_cash "Cash"
label var r_security "Security"
	label var r_treasury "Treasury" 
	label var r_rmbs " RMBS" //Hold-to-Maturity+Available to sell
	label var r_cmbs "CMBS"
	label var r_abs "ABS"
	label var r_other_sec "Other Security"

label var r_fed_fund_sold "Fed Funds Sold"
label var r_rerepo "Reverse Repo"

label var r_total_loan "Total Loan"
	label var r_reloan "Real Estate Loan"
		label var r_reloan_land_dev "Land Development Loan"
		label var r_reloan_farmland "Farm Land Loan"
		label var r_reloan_residential1to4 "Residential Mortgage"
		label var r_reloan_residential5 "Commercial Mortgage"
		label var r_other_reloan "Other Real Estate Loan"
	label var r_agloan "Agricultural Loan"
	label var r_ciloan "Commercial Industrial Loan"
	label var r_hhloan "Consumer Loan"
	label var r_loans_to_nonbank "Loan to Non-Depository Financial Institution"
	label var r_other_loan "Other Loan"

label var r_trading_asset "Trading Asset"
label var r_fixed_asset "Fixed Asset"
label var r_other_asset "Other Asset"

label var r_total_liability "Total Liability"
label var r_dom_deposit "Domestic Deposit"
label var r_insured_deposit "Insured Deposit"
label var r_uninsured_deposit "Uninsured Deposit"
label var r_insured_deposit_account "Insured Deposit Account"
label var r_uninsured_deposit_account "Uninsured Deposit Account"

label var r_foreign_deposit "Foreign Deposit"
label var r_fed_fund_purchase "Fed Fund Purchase"
label var r_repo "Repo"
label var r_other_liab "Other Liability"

label var r_total_equity "Total Equity"
label var r_common_stock "Common Stock"
label var r_preferred_stock "Preferred Stock"
label var r_retained_earning "Retained Earning"


// variable by maturity 
label var r_treasury_3mo "Less than 3 Months"
label var r_treasury_1yr "3 Months to 1 Year"
label var r_treasury_3yr "1 Year to 3 Years"
label var r_treasury_5yr "3 Years to 5 Years"
label var r_treasury_15yr "5 Years to 15 Years"
label var r_treasury_20yr "20+ Years"

label var r_rmbs_3mo "Less than 3 Months"
label var r_rmbs_1yr "3 Months to 1 Year"
label var r_rmbs_3yr "1 Year to 3 Years"
label var r_rmbs_5yr "3 Years to 5 Years"
label var r_rmbs_15yr "5 Years to 15 Years"
label var r_rmbs_20yr "20+ Years"

label var r_other_rmbs_3yrless "Less than 3 Years"
label var r_other_rmbs_3yrmore "More than 3 Years"


// loans - r-mortgage

label var r_reloan1to4_3mo "Less than 3 Months"
label var r_reloan1to4_1yr "3 Months to 1 Year"
label var r_reloan1to4_3yr "1 Year to 3 Years"
label var r_reloan1to4_5yr "3 Years to 5 Years"
label var r_reloan1to4_15yr "5 Years to 15 Years"
label var r_reloan1to4_20yr "20+ Years"

// loans - other
label var r_all_other_loan_3mo "Less than 3 Months"
label var r_all_other_loan_1yr "3 Months to 1 Year"
label var r_all_other_loan_3yr "1 Year to 3 Years"
label var r_all_other_loan_5yr "3 Years to 5 Years"
label var r_all_other_loan_15yr "5 Years to 15 Years"
label var r_all_other_loan_20yr "20+ Years"

// Other borrowing
label var other_borrowing_1yr "Less than 1 year"

// [New] Interest expense, Interest income
label var interest_expense "Interest Expense"
label var interest_income "Interest Income"
label var interest_income_net "Net Interest Income"

// [New] Return on assets
label var net_income "Net Income"
label var earning_assets "Earning Assets"

// Cleaning to remove outliers for figures and regressions
drop if r_total_equity<0 | r_total_equity>100
drop if r_total_liability<0 | r_total_liability>100
drop if total_asset<0

replace r_foreign_deposit = 0 if missing(r_foreign_deposit)

save,replace 


//// ///////// clean 2022Q1 

use "${raw}callreport_2022q1_clean.dta",clear 

// create balance sheet
// asset ratios

foreach x of var cash security *_amortize *_fair  fed_fund_sold rerepo total_loan total_loan_hfs total_loan_hfi reloan* agloan ciloan hhloan loans_to_nonbank other_loan trading_asset fixed_asset {
gen r_`x' = 100*`x'/total_asset
replace r_`x' = 0 if missing(r_`x')
}

drop r_reloan1to4_*

// find the percentage of each type of asset by maturity 
foreach x of var t*_3mo t*_1yr t*_3yr t*_5yr t*_15yr t*_20yr  rm*_3mo rm*_1yr rm*_3yr rm*_5yr rm*_15yr rm*_20yr other_rmbs_3yrless other_rmbs_3yrmore  {
gen r_`x' = 100*`x'/security 
}

foreach x of var reloan*_3mo reloan*_1yr reloan*_3yr reloan*_5yr reloan*_15yr reloan*_20yr a*_3mo a*_1yr a*_3yr a*_5yr a*_15yr a*_20yr{
gen r_`x' = 100*`x'/total_loan 
}



gen r_other_reloan = r_reloan - r_reloan_residential1to4-r_reloan_residential5 
gen r_other_asset = 100-r_cash-r_security-r_fed_fund_sold-r_rerepo-r_total_loan-r_trading_asset 
foreach x in "treasury" "agency_rmbs" "other_rmbs" "agency_cmbs" "other_cmbs" "abs"{
gen r_`x' = r_`x'_htm_amortize+r_`x'_hfs_fair
}
gen r_rmbs = r_agency_rmbs + r_other_rmbs 
gen r_cmbs = r_agency_cmbs + r_other_cmbs 
gen r_other_sec = r_security - r_treasury - r_rmbs - r_cmbs -  r_abs 

// liability + equity 
foreach x of var total_liability total_equity preferred_stock common_stock surplus retained_earning total_bank_equity  dom_deposit foreign_deposit insured_deposit* uninsured_deposit*  fed_fund_purchase repo trading_liab other_borrowed_money subordinated_debt other_liab {
gen r_`x' = 100*`x'/total_asset
}

replace r_other_liab = r_other_liab+r_trading_liab+r_other_borrowed_money+r_subordinated_debt
sum total_asset, detail 

// define small, large  https://www.ffiec.gov/cra/pdf/2023_Asset_Size_Threshold.pdf; https://www.federalreserve.gov/newsevents/pressreleases/bcreg20211216a.htm
egen size = mean(total_asset), by(idrssd)
gen size_bin = "small" if size<1384000 // 1.384B
replace size_bin = "gsib" if size>250000000 // 250B
replace size_bin = "large" if missing(size_bin)

// label variable 
replace total_asset = total_asset/1000000
label var total_asset "Total Asset (Billion)"
label var r_cash "Cash"
label var r_security "Security"
	label var r_treasury "Treasury" 
	label var r_rmbs " RMBS" //Hold-to-Maturity+Available to sell
	label var r_cmbs "CMBS"
	label var r_abs "ABS"
	label var r_other_sec "Other Security"

label var r_fed_fund_sold "Fed Funds Sold"
label var r_rerepo "Reverse Repo"

label var r_total_loan "Total Loan"
	label var r_reloan "Real Estate Loan"
		label var r_reloan_land_dev "Land Development Loan"
		label var r_reloan_farmland "Farm Land Loan"
		label var r_reloan_residential1to4 "Residential Mortgage"
		label var r_reloan_residential5 "Commercial Mortgage"
		label var r_other_reloan "Other Real Estate Loan"
	label var r_agloan "Agricultural Loan"
	label var r_ciloan "Commercial Industrial Loan"
	label var r_hhloan "Consumer Loan"
	label var r_loans_to_nonbank "Loan to Non-Depository Financial Institution"
	label var r_other_loan "Other Loan"

label var r_trading_asset "Trading Asset"
label var r_fixed_asset "Fixed Asset"
label var r_other_asset "Other Asset"

label var r_total_liability "Total Liability"
label var r_dom_deposit "Domestic Deposit"
label var r_insured_deposit "Insured Deposit"
label var r_uninsured_deposit "Uninsured Deposit"
label var r_insured_deposit_account "Insured Deposit Account"
label var r_uninsured_deposit_account "Uninsured Deposit Account"

label var r_foreign_deposit "Foreign Deposit"
label var r_fed_fund_purchase "Fed Fund Purchase"
label var r_repo "Repo"
label var r_other_liab "Other Liability"

label var r_total_equity "Total Equity"
label var r_common_stock "Common Stock"
label var r_preferred_stock "Preferred Stock"
label var r_retained_earning "Retained Earning"


// variable by maturity 
label var r_treasury_3mo "Less than 3 Months"
label var r_treasury_1yr "3 Months to 1 Year"
label var r_treasury_3yr "1 Year to 3 Years"
label var r_treasury_5yr "3 Years to 5 Years"
label var r_treasury_15yr "5 Years to 15 Years"
label var r_treasury_20yr "20+ Years"

label var r_rmbs_3mo "Less than 3 Months"
label var r_rmbs_1yr "3 Months to 1 Year"
label var r_rmbs_3yr "1 Year to 3 Years"
label var r_rmbs_5yr "3 Years to 5 Years"
label var r_rmbs_15yr "5 Years to 15 Years"
label var r_rmbs_20yr "20+ Years"

label var r_other_rmbs_3yrless "Less than 3 Years"
label var r_other_rmbs_3yrmore "More than 3 Years"


// loans - r-mortgage

label var r_reloan1to4_3mo "Less than 3 Months"
label var r_reloan1to4_1yr "3 Months to 1 Year"
label var r_reloan1to4_3yr "1 Year to 3 Years"
label var r_reloan1to4_5yr "3 Years to 5 Years"
label var r_reloan1to4_15yr "5 Years to 15 Years"
label var r_reloan1to4_20yr "20+ Years"

// loans - other
label var r_all_other_loan_3mo "Less than 3 Months"
label var r_all_other_loan_1yr "3 Months to 1 Year"
label var r_all_other_loan_3yr "1 Year to 3 Years"
label var r_all_other_loan_5yr "3 Years to 5 Years"
label var r_all_other_loan_15yr "5 Years to 15 Years"
label var r_all_other_loan_20yr "20+ Years"

// Other borrowing
label var other_borrowing_1yr "Less than 1 year"

// [New] Interest expense, Interest income
label var interest_expense "Interest Expense"
label var interest_income "Interest Income"
label var interest_income_net "Net Interest Income"

// [New] Return on assets
label var net_income "Net Income"
label var earning_assets "Earning Assets"

// Cleaning to remove outliers for figures and regressions
drop if r_total_equity<0 | r_total_equity>100
drop if r_total_liability<0 | r_total_liability>100
drop if total_asset<0

replace r_foreign_deposit = 0 if missing(r_foreign_deposit)

save,replace 